import 'dart:async';
import 'package:flutter/services.dart';
import 'package:flutter/widgets.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

/// 基于SQLite本地数据库操作
class DatabaseUtil {
  static Database? _database;

  /// 获取数据库实例
  static Future<Database> getInstance() async {
    if (_database != null) return _database!;
    _database = await _initDB();
    return _database!;
  }

  /// 初始化数据库
  static Future<Database> _initDB() async {
    String path = join(await getDatabasesPath(), 'app.db');
    return await openDatabase(
      path,
      version: 1,
      onCreate: (db, version) async {
        // 确保Widgets框架初始化完成
        WidgetsFlutterBinding.ensureInitialized();
        for (int i = 1; i <= version; i++) {
          try {
            String sqlScript = await rootBundle.loadString('lib/storage/migrations/${i}.sql');
            await db.execute(sqlScript);
          } catch(e) {
            print(e);
          }
        }
        return;
      },
      onUpgrade: (db, oldVersion, newVersion) async {
        // 确保Widgets框架初始化完成
        WidgetsFlutterBinding.ensureInitialized();
        for (int i = oldVersion + 1; i <= newVersion; i++) {
          try {
            String sqlScript = await rootBundle.loadString('lib/storage/migrations/${i}.sql');
            await db.execute(sqlScript);
          } catch(e) {
            print(e);
          }
        }
        return;
      }
    );
  }
}

/// 模型抽象类
abstract class Model {
  /// 表名
  late String tableName;

  /// 主键列名
  String primaryColumnName = 'id';

  Future<Database> database() async {
    return DatabaseUtil.getInstance();
  }

  /// 插入数据
  Future<int> insertItem(Map<String, dynamic> item) async {
    final db = await database();
    return await db.insert(tableName, item);
  }

  /// 查询所有数据
  Future<List<Map<String, dynamic>>> queryAllItems() async {
    final db = await database();
    return await db.query(tableName);
  }

  /// 根据条件查询数据
  Future<List<Map<String, Object?>>> queryItems({
    bool? distinct,
    List<String>? columns,
    String? where,
    List<Object?>? whereArgs,
    String? groupBy,
    String? having,
    String? orderBy,
    int? limit,
    int? offset,
  }) async {
    final db = await database();
    return await db.query(
      tableName,
      distinct: distinct,
      columns: columns,
      where: where,
      whereArgs: whereArgs,
      groupBy: groupBy,
      having: having,
      orderBy: orderBy,
      limit: limit,
      offset: offset,
    );
  }

  /// 根据 ID 查询数据
  Future<Map<String, dynamic>?> queryItem(int id) async {
    final db = await database();
    List<Map<String, dynamic>> maps = await db.query(
      tableName,
      where: '${primaryColumnName} = ?',
      whereArgs: [id],
    );
    if (maps.isNotEmpty) {
      return maps.first;
    }
    return null;
  }

  /// 更新数据
  Future<int> updateItem(Map<String, dynamic> item) async {
    final db = await database();
    return await db.update(
      tableName,
      item,
      where: '${primaryColumnName} = ?',
      whereArgs: [item['id']],
    );
  }

  /// 批量更新数据
  Future<int> updateBatch(String? where, List<Object?>? whereArgs, Map<String, Object?> values) async {
    final db = await database();
    return await db.update(
      tableName,
      values,
      where: where,
      whereArgs: whereArgs,
    );
  }

  /// 删除数据
  Future<int> deleteItem(int id) async {
    final db = await database();
    return await db.delete(
      tableName,
      where: '${primaryColumnName} = ?',
      whereArgs: [id],
    );
  }

  /// 批量删除数据
  Future<int> deleteBatch(List<int> ids) async {
    final db = await database();
    String placeholders = List.filled(ids.length, '?').join(', ');
    return await db.delete(
      tableName,
      where: '${primaryColumnName} in (${placeholders})',
      whereArgs: ids,
    );
  }
}
